from CourseRegistration import db  # db的创建在__init__.py里，所以这里用项目包名，使其能在flask shell里执行

# from models import Professor, Student, Course, Registrar, Prerequisite, Inputbox, Bill, Takes, Teaches, Section

from flask import Flask
# from . import db, login_manager
from CourseRegistration.models import Professor, Student, Course, Registrar, Prerequisite, Inputbox, Bill, Takes, \
    Teaches, Section, Openingconfig
from tqdm import tqdm
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

db.drop_all()
db.create_all()

# 添加学生
inPath = "csvFile/student.csv"
f = open(inPath, "r", encoding='GBK')
students = []
for line in f.readlines():
    line = line.strip().split(",")
    student_id, student_name, date_of_birth, social_security_number, status, graduation_date, department, \
    balance, password = int(line[0]), line[1], line[2], line[3], line[4], line[5], line[6], float(line[7]), line[8]
    tmp = int(date_of_birth)
    date_of_birth = datetime(tmp // 10000, tmp // 100 % 100, tmp % 100)
    tmp = int(graduation_date)
    graduation_date = datetime(tmp // 10000, tmp // 100 % 100, tmp % 100)
    student = Student(student_id=student_id, student_name=student_name, date_of_birth=date_of_birth,
                      social_security_number=social_security_number, status=status, graduation_date=graduation_date,
                      department=department, balance=balance, password=password)
    students.append(student)
db.session.add_all(students)

# 添加教授
inPath = "csvFile/professor.csv"
f = open(inPath, "r", encoding='GBK')
professors = []
for line in f.readlines():
    line = line.strip().split(",")
    professor_id, professor_name, date_of_birth, social_security_number, status, department, password \
        = int(line[0]), line[1], line[2], line[3], line[4], line[5], line[6]
    tmp = int(date_of_birth)
    date_of_birth = datetime(tmp // 10000, tmp // 100 % 100, tmp % 100)
    professor = Professor(professor_id=professor_id, professor_name=professor_name, date_of_birth=date_of_birth,
                          social_security_number=social_security_number, status=status, department=department,
                          password=password)
    professors.append(professor)
db.session.add_all(professors)

# 添加管理员
inPath = "csvFile/registrar.csv"
f = open(inPath, "r", encoding='GBK')
registrars = []
for line in f.readlines():
    line = line.strip().split(",")
    registrar_id, registrar_name, date_of_birth, social_security_number, status, password \
        = int(line[0]), line[1], line[2], line[3], line[4], line[5]
    tmp = int(date_of_birth)
    date_of_birth = datetime(tmp // 10000, tmp // 100 % 100, tmp % 100)
    registrar = Registrar(registrar_id=registrar_id, registrar_name=registrar_name, date_of_birth=date_of_birth,
                          social_security_number=social_security_number, status=status, password=password)
    registrars.append(registrar)
db.session.add_all(registrars)

# 添加课程信息
inPath = "csvFile/course.csv"
f = open(inPath, "r", encoding='GBK')
courses = []
for line in f.readlines():
    line = line.strip().split(",")
    course_id, course_name, department, description, credit = int(line[0]), line[1], line[2], line[3], float(line[4])
    course = Course(course_id=course_id, course_name=course_name, department=department, description=description,
                    credit=credit, price=credit * 100)
    courses.append(course)
db.session.add_all(courses)

# 添加先修课程
inPath = "csvFile/prerequisite.csv"
f = open(inPath, "r", encoding='GBK')
prerequisites = []
for line in f.readlines():
    line = line.strip().split(",")
    id, pre_id, course_id = int(line[0]), int(line[1]), int(line[2])
    prerequisite = Prerequisite(id=id, pre_id=pre_id, course_id=course_id)
    prerequisites.append(prerequisite)
db.session.add_all(prerequisites)

# 添加学期信息
inPath = "csvFile/section.csv"
f = open(inPath, "r", encoding='GBK')
sections = []
for line in f.readlines():
    line = line.strip().split(",")
    section_id, course_id, semester = int(line[0]), int(line[1]), line[2]
    section = Section(section_id=section_id, course_id=course_id, semester=semester)
    sections.append(section)
db.session.add_all(sections)

# 添加takes信息
inPath = "csvFile/takes.csv"
f = open(inPath, "r", encoding='GBK')
takesList = []
for line in f.readlines():
    line = line.strip().split(",")
    takes_id, section_id, student_id, course_id = int(line[0]), int(line[1]), int(line[2]), int(line[3])
    takes = Takes(takes_id=takes_id, student_id=student_id, course_id=course_id, section_id=section_id)
    takesList.append(takes)
db.session.add_all(takesList)

# 添加teaches信息
inPath = "csvFile/teaches.csv"
f = open(inPath, "r", encoding='GBK')
teachesList = []
for line in f.readlines():
    line = line.strip().split(",")
    teaches_id, professor_id, course_id, section_id = int(line[0]), int(line[1]), int(line[2]), int(line[3])
    teaches = Teaches(teaches_id=teaches_id, professor_id=professor_id, course_id=course_id, section_id=section_id)
    teachesList.append(teaches)
db.session.add_all(teachesList)

db.session.commit()

# 添加bill信息
takes = Takes.query.all()
bill_list = [(t.student_id, t.course_id, t.course.price) for t in takes]
for i in range(len(bill_list)):
    bill = Bill(bill_id=i + 1, student_id=bill_list[i][0], course_id=bill_list[i][1], course_price=bill_list[i][2],
                hasBeenPaid=False)
    db.session.add(bill)
db.session.commit()

opening = Openingconfig(opening_id=1, value=True)
db.session.add(opening)
db.session.commit()
